<?php

/**
 * Average studentactivity remuneration per company
 * @package ModuleStatistiques
 * @version 1.0b
 * @return JSON
 */

session_start();

// Configuration file
include '../../configuration.php';
// Functions
include '../../inc/functions.php';

// Checking if user is logged in
if (!CheckSession()):
	exit();
endif;

// Database connexion string
$db_str = "mysql:dbname=".$configuration['db'].";host=".$configuration['host'];

// Collecting numeric stats
try {
	
	// Connecting to database
	$db = new PDO($db_str, $configuration['user'], $configuration['pass']);
	
	
	// Query
	$sql  = 'SELECT company.name AS "COMPANY" ,remuneration.type AS "TYPE", CASE remuneration.type WHEN "DAY" THEN CASE WHEN ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) <= 30 THEN ROUND(AVG(remuneration.amount) * ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0),0) ELSE ROUND(AVG(remuneration.amount) * ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) * 30 / ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0), 0) END WHEN "WEEK" THEN CASE WHEN (ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) /7) <= 4 THEN ROUND(AVG(remuneration.amount) *(ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) /7 ),0) ELSE ROUND(ROUND(AVG(remuneration.amount) *(ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) /7 ),0) / 4 ,0) END WHEN "2WEEK" THEN CASE WHEN (ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) / 15) <= 2 THEN ROUND(AVG(remuneration.amount) *(ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) / 15 ),0) ELSE ROUND(ROUND(AVG(remuneration.amount) *(ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) /15 ),0) /2,0) END WHEN "MONTH" THEN CASE WHEN ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) <= 30 THEN ROUND(AVG(remuneration.amount),0) ELSE ROUND(AVG(remuneration.amount),0) END WHEN "2MONTH" THEN CASE WHEN ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) <= 60 THEN ROUND(ROUND(AVG(remuneration.amount),0) /2,0) ELSE ROUND(ROUND(AVG(remuneration.amount),0) /2,0) END WHEN "ALL" THEN CASE WHEN ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0) <= 30 THEN ROUND(AVG(remuneration.amount),0) ELSE ROUND(AVG(remuneration.amount) / (ROUND(AVG(DATEDIFF(gotstudentactivity.to, gotstudentactivity.from)), 0)/30),0) END END AS "MONTANT MENSUEL" ';
	$sql .= 'FROM gotstudentactivity INNER JOIN company ON gotstudentactivity.companyId = company.companyId INNER JOIN remuneration ON gotstudentactivity.remunerationId = remuneration.remunerationId ';
	$sql .= 'GROUP BY company.name, remuneration.type;';
	
	// Executing query
	$query = $db->prepare($sql);
	$query->execute();
	
	// Fetching data
	$result = $query->fetchAll(PDO::FETCH_ASSOC);
	
	// Rendering data
	$categories = $initiate = $series = Array();
	
	foreach ($result as $item) {
		if (!in_array($item['TYPE'], $categories)) {
			array_push($categories, $item['TYPE']);
			array_push($initiate, 0);
		}
	}
	
	foreach ($result as $item) {
		$k = 0;
		$size = count($series);
		while ($k < $size) {
			if ($series[$k]['name'] == $item['COMPANY'])
				break;
			++$k;
		}
		if ($k == $size)
			array_push($series, Array('name' => $item['COMPANY'], 'data' => $initiate));
		
		$k = 0;
		$size = count($series);
		$sizeCats = count($categories);
		for ($i = 0; $i < $sizeCats; ++$i) {
			if ($item['TYPE'] == $categories[$i]) {
				while ($k < $size) {
					if ($series[$k]['name'] == $item['COMPANY'])
						$series[$k]['data'][$i] = intval($item['MONTANT MENSUEL']);
					++$k;
				}
			}
		}
	}
	
	echo json_encode($series);
	
} catch (Exception $e) {
	echo $e->getMessage();
}

?>